#4-3
import pandas
import pandas as pd
pd.set_option('display.unicode.east_asian_width',True)
df= pd.DataFrame([2,5,5,5,10,3,4,12,7,10],columns=['原始数据'])
df['顺序排名'] = df['原始数据'].rank(method='first')
df['最大值排名'] = df['原始数据'].rank(method='max')
df['最小值排名'] = df['原始数据'].rank(method='min')
df['平均值排名'] = df['原始数据'].rank(method='average')
print(df)

#4-4
import pandas as pd
pd.set_option('display.unicode.east_asian_width',True)
df = pd.read_excel('学生成绩表.xlsx')
df['总成绩']= df.groupby({'语文':'总成绩','数学':'总成绩','英语':'总成绩','综合':'总成绩'},axis=1).agg('sum')
df['排名']=df['总成绩'].rank(method='min',ascending=False)
df.sort_values('排名',inplace=True,ignore_index=True)
df1 = pd.DataFrame()
groups = df.groupby('排名')
for group in groups:
    df2 = pd.DataFrame(group[1])
    df2.sort_values('语文',ascending=False,inplace=True)
    df1 = pd.concat([df1,df2])
df1 = df1.reset_index(drop=True)
print(df1)

#4-5
import pandas as pd
pd.set_option('display.unicode.east_asian_width',True)
df = pd.read_excel('学生成绩表.xlsx')
df['总成绩']= df.sum(axis=1,numeric_only=True)
df['平均成绩']=df.mean(axis=1,numeric_only=True)
print(df)
print(df.describe())

#4-6
import pandas as pd
pd.set_option('display.unicode.east_asian_width',True)
df = pd.DataFrame([['A','C','B'],['B','A','C'],['A','B','C'],['A','B','C']],columns=['a','b','c'])
print('原始数据:\n',df)
print('按频率降序统计a列：\n',df.value_counts('a'))
print('按频数降序统计所有列：\n',df.value_counts())
print('按频率升序统计b列：\n',df.value_counts('b',normalize=True,ascending=True))
print('使用dscribe()函数统计所有列：\n',df.describe())

#4-7
import pandas as pd
pd.set_option('display.unicode.east_asian_width',True)
df = pd.read_excel('产品订单信息表.xlsx')
df1 = pd.crosstab(index=df['性别'],columns=df['产品类型'])
print('统计性别和商品类型交叉频数的数据df1:\n',df1)
df2 = pd.crosstab(index=df['性别'],columns=df['产品类型'],margins=)